import pandas as pd
import sys
from collections import Counter

# ==============================
# 分类配置（含负责人）
# ==============================
CATEGORIES = {
    '水果': {
        'items': {'Apple', 'Orange', 'Mango', '香蕉', '苹果'},
        'owner': '张三',
        'emp_id': 'E1001'
    },
    '蔬菜': {
        'items': {'Carrot', '土豆', '菠菜', '白菜','Potato'},
        'owner': '李四',
        'emp_id': 'E1002'
    },
    '肉类': {
        'items': {'Pork', 'Beef', '鸡肉'},
        'owner': '王五',
        'emp_id': 'E1003'
    },
    '海鲜': {
        'items': {'Salmon', '虾', 'Tuna'},
        'owner': '赵六',
        'emp_id': 'E1004'
    }
}

# 构建反向映射
ITEM_TO_CATEGORY = {}
for category, config in CATEGORIES.items():
    for item in config['items']:
        if item in ITEM_TO_CATEGORY:
            print(f"⚠️ 警告: '{item}' 重复定义")
        else:
            ITEM_TO_CATEGORY[item] = category

# ==============================
# 统计函数
# ==============================
def count_with_owner(file_path, sheet_name=0):
    df = pd.read_excel(file_path, sheet_name=sheet_name, dtype=str, keep_default_na=False)
    if df.shape[1] < 7:
        raise ValueError("Excel 至少需要7列（A~G）")

    f_col = df.iloc[:, 5]
    g_col = df.iloc[:, 6]

    mask = (g_col == '') | (g_col.isna())
    filtered_f = f_col[mask].dropna()
    filtered_f = filtered_f[filtered_f != ''][filtered_f != 'nan']

    counts = Counter()
    unknown_items = []

    for item in filtered_f:
        clean = str(item).strip()
        cat = ITEM_TO_CATEGORY.get(clean)
        if cat is not None:
            counts[cat] += 1
        else:
            unknown_items.append(clean)

    unknown_detail = Counter(unknown_items) if unknown_items else None
    return counts, unknown_detail, len(filtered_f)

# ==============================
# 主程序
# ==============================
if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("用法: python count_with_owner.py <excel_file>")
        sys.exit(1)

    file_path = sys.argv[1]
    try:
        counts, unknown, total = count_with_owner(file_path)

        print(f"\n📊【G列为空的有效记录总数】: {total}")
        print("\n✅【分类统计结果】\n")

        # 只打印计数 > 0 的类别（按 CATEGORIES 定义顺序）
        printed_any = False
        for category, config in CATEGORIES.items():
            cnt = counts.get(category, 0)
            if cnt > 0:
                owner = config['owner']
                emp_id = config['emp_id']
                print(f"{category}: {cnt} 条 | 负责人: {owner} ({emp_id})")
                printed_any = True

        if not printed_any:
            print("（无匹配的已知类别）")

        # 打印未知项（如果有）
        if unknown:
            print(f"\n❓【未知项（共 {sum(unknown.values())} 条）】")
            for value, cnt in unknown.most_common():
                print(f"  ├─ '{value}' → {cnt}")

        print()
    except Exception as e:
        print(f"❌ 错误: {e}")